﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using DevComponents.DotNetBar.Controls;

namespace LanAnh
{
    class Data
    {
        private static SqlConnection conn = new SqlConnection("Data Source = .\\SQLEXPRESS;Initial Catalog=NhanKhau;User Id=sa;Password=123;");
        private SqlCommand command;
        private SqlDataAdapter adapter;
        private DataSet ds;
        public Data()
        {
            MyServerConfig.InitServer("DataConfig.xml");
            ChangeConnection(MyServerConfig.SServerAdd);
        }
        public bool CheckConnection()
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                    return true;
                conn.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool ChangeConnection(String sql)
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.ConnectionString = sql;
                conn.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }
        private void CloseConnection()
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            Status.Complete();
        }
        private void OpenConnection()
        {
            
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            else
            {
                conn.Close();
                conn.Open();
            }
            Status.Process();
        }
        public void executeNonQuery(string sqlString)
        {
            //Status.Process();
            OpenConnection();
            command = new SqlCommand(sqlString, conn);
            command.ExecuteNonQuery();
            //Status.Complete();
        }
        public DataTable executeQueryTable(string sqlString)
        {
            //Status.Process();
            try
            {
                OpenConnection();
                DataSet ds = new DataSet();
                adapter = new SqlDataAdapter(sqlString, conn);
                adapter.Fill(ds);
                Status.Complete();
                return ds.Tables[0];
                
            }
            catch
            {
                //Status.Complete();
                return null;
            }
            
        }
        public DataSet getTable(string Table)
        {
            //Status.Process();
            OpenConnection();
            ds = new DataSet();
            adapter = new SqlDataAdapter();
            adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("Select * From " + Table, conn);
            adapter.Fill(ds, Table);
            //Status.Complete();
            return ds;
        }
        public void HienThiComboBox(ComboBoxEx comboBox, string Table, string cMa, string cTen)
        {
            //Status.Process();
            comboBox.DataSource = getTable(Table).Tables[0];
            comboBox.DisplayMember = cTen;
            comboBox.ValueMember = cMa;
            //Status.Complete();
        }
        public void HienThiComboBox(ComboBoxEx comboBox, DataTable  dataTable, string cMa, string cTen)
        {
            comboBox.DataSource = dataTable;
            comboBox.DisplayMember = cTen;
            comboBox.ValueMember = cMa;
        }
        public void autoComplete(ComboBox cb, string table, string column)
        {
            //Status.Process();
            //beging autocomplete
            AutoCompleteStringCollection autocompleteCollection = new AutoCompleteStringCollection();
            DataTable dt = getTable(table).Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                autocompleteCollection.Add(dt.Rows[i][column].ToString());
            }
            cb.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cb.AutoCompleteSource = AutoCompleteSource.CustomSource;
            cb.AutoCompleteCustomSource = autocompleteCollection;
            //end autocomplete
            //Status.Complete();
        }
        public void autoComplete(ComboBox cb, DataTable dt, string column)
        {
            //Status.Process();
            //beging autocomplete
            AutoCompleteStringCollection autocompleteCollection = new AutoCompleteStringCollection();            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                autocompleteCollection.Add(dt.Rows[i][column].ToString());
            }
            cb.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cb.AutoCompleteSource = AutoCompleteSource.CustomSource;
            cb.AutoCompleteCustomSource = autocompleteCollection;
            //end autocomplete
            //Status.Complete();
        }
        public void autoComplete(TextBox txt, string table, string column)
        {
            //Status.Process();
            //beging autocomplete
            AutoCompleteStringCollection autocompleteCollection = new AutoCompleteStringCollection();
            DataTable dt = getTable(table).Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                autocompleteCollection.Add(dt.Rows[i][column].ToString());
            }
            txt.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            txt.AutoCompleteSource = AutoCompleteSource.CustomSource;
            txt.AutoCompleteCustomSource = autocompleteCollection;
            //end autocomplete
            //Status.Complete();
        }
        public void autoComplete(TextBox txt, DataTable dt, string column)
        {
            //Status.Process();
            //beging autocomplete
            AutoCompleteStringCollection autocompleteCollection = new AutoCompleteStringCollection();            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                autocompleteCollection.Add(dt.Rows[i][column].ToString());
            }
            txt.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            txt.AutoCompleteSource = AutoCompleteSource.CustomSource;
            txt.AutoCompleteCustomSource = autocompleteCollection;
            //end autocomplete
            //Status.Complete();
        }
    }
}
